package com.cloud.system.utils.excel;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.cloud.common.core.asserts.ApiAssert;
import com.cloud.common.core.enums.ErrorCodeEnum;
import com.cloud.common.core.utils.file.FileUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import static org.apache.poi.ss.usermodel.CellType.*;

/**
 * 导入导出
 */
@Slf4j
public class ExcelUtils {

    /**
     * 导出Excel
     *
     * @param request
     * @param response
     * @param data
     */
    public static void exportExcel(HttpServletRequest request, HttpServletResponse response, ExcelData data) {
        log.info("导出解析开始，fileName:{}", data.getFileName());
        try {
            //实例化HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个Excel表单，参数为sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet");
            //设置表头
            setTitle(workbook, sheet, data.getHead());
            //设置单元格并赋值
            setData(sheet, data.getData());
            //设置浏览器下载
            setBrowser(request, response, workbook, data.getFileName());
            log.info("导出解析成功!");
        } catch (Exception e) {
            log.info("导出解析失败!");
            e.printStackTrace();
        }
    }

    /**
     * 设置表头
     *
     * @param workbook
     * @param sheet
     * @param heads
     */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, List<String> heads) {
        try {
            HSSFRow row = sheet.createRow(0);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= heads.size(); i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //创建表头名称
            HSSFCell cell;
            for (int j = 0; j < heads.size(); j++) {
                cell = row.createCell(j);
                cell.setCellValue(heads.get(j));
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("导出时设置表头失败！");
            e.printStackTrace();
        }
    }

    /**
     * 表格赋值
     *
     * @param sheet
     * @param data
     */
    private static void setData(HSSFSheet sheet, List<List<String>> data) {
        try {
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).size(); j++) {
                    row.createCell(j).setCellValue(data.get(i).get(j));
                }
                rowNum++;
            }
            log.info("表格赋值成功！");
        } catch (Exception e) {
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }

    /**
     * 使用浏览器下载
     *
     * @param response
     * @param workbook
     * @param fileName
     */
    private static void setBrowser(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header  前端乱码需要转码 fileName = window.decodeURI(res.headers['content-disposition'].split('=')[1], "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + FileUtils.setFileDownloadHeader(request, fileName));
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("设置浏览器下载成功！");
        } catch (Exception e) {
            log.info("设置浏览器下载失败！");
            e.printStackTrace();
        }

    }


    /**
     * 导入
     * 读取所有数据 不包含表头
     *
     * @param file
     * @return
     */
    public static ExcelData importExcelOne(MultipartFile file) {
        String filename = file.getOriginalFilename();
        log.info("导入解析开始，fileName:{}", filename);
        ExcelData excelData = new ExcelData();
        try {
            //表头信息
            List<String> headList = new ArrayList<>();
            //数据信息
            List<List<String>> dataList = new ArrayList<>();
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            //获取sheet的行数
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                //获取当前行的数据
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                List<String> rowList = new ArrayList<>();
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (i == 0) {
                        resolveHead(cell, headList);
                    } else {
                        resolveData(cell, rowList);
                    }
                }
                if (i != 0) {
                    dataList.add(rowList);
                }

            }
            log.info("导入文件解析成功！");
            excelData.setFileName(filename);
            excelData.setHead(headList);
            excelData.setData(dataList);
            return excelData;
        } catch (Exception e) {
            log.error("导入文件解析失败！", e);
            ApiAssert.failure(ErrorCodeEnum.INTERNAL_SERVER_ERROR.convert("导入文件解析失败"));
        }
        return null;
    }

    /**
     * 导入
     * 读取所有数据 不包含表头
     *
     * @param file
     * @return
     */
    public static List<ExcelData> importExcelAll(MultipartFile file) {
        String filename = file.getOriginalFilename();
        log.info("导入解析开始，fileName:{}", filename);
        List<ExcelData> resList = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(file.getInputStream());
            for (int k = 0; k < workbook.getNumberOfSheets(); k++) {
                //表头信息
                List<String> headList = new ArrayList<>();
                //数据信息
                List<List<String>> dataList = new ArrayList<>();
                ExcelData excelData = new ExcelData();
                Sheet sheet = workbook.getSheetAt(k);
                //获取sheet的行数
                int rows = sheet.getPhysicalNumberOfRows();
                for (int i = 0; i < rows; i++) {
                    //获取当前行的数据
                    Row row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    List<String> rowList = new ArrayList<>();
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        resolveData(cell, rowList);
                    }
                    if (CollectionUtils.isNotEmpty(rowList)) {
                        dataList.add(rowList);
                    }

                }
                log.info("导入文件解析成功！");
                excelData.setFileName(filename);
                excelData.setHead(headList);
                excelData.setData(dataList);
                resList.add(excelData);
            }
        } catch (Exception e) {
            log.error("导入文件解析失败！", e);
            ApiAssert.failure(ErrorCodeEnum.INTERNAL_SERVER_ERROR.convert("导入文件解析失败"));
        }
        return resList;
    }


    /**
     * 解析头部信息
     *
     * @param cell
     * @param headList
     */
    private static void resolveHead(Cell cell, List<String> headList) {
        if (Objects.isNull(cell)) {
            headList.add("");
            return;
        }
        if (cell.getCellType().equals(NUMERIC)) {
            headList.add(String.valueOf(cell.getNumericCellValue()).trim());
        } else if (cell.getCellType().equals(STRING)) {
            headList.add(cell.getStringCellValue().trim());
        } else if (cell.getCellType().equals(BOOLEAN)) {
            headList.add(String.valueOf(cell.getBooleanCellValue()).trim());
        } else if (cell.getCellType().equals(ERROR)) {
            //异常数据返回空
            headList.add("");
        } else {
            headList.add("");
        }
    }

    /**
     * 解析内容信息
     *
     * @param cell
     * @param rowList
     */
    private static void resolveData(Cell cell, List<String> rowList) {
        if (Objects.isNull(cell)) {
            rowList.add("");
            return;
        }
        if (cell.getCellType().equals(NUMERIC)) {
            rowList.add(String.valueOf(cell.getNumericCellValue()).trim());
        } else if (cell.getCellType().equals(STRING)) {
            rowList.add(cell.getStringCellValue().trim());
        } else if (cell.getCellType().equals(BOOLEAN)) {
            rowList.add(String.valueOf(cell.getBooleanCellValue()).trim());
        } else if (cell.getCellType().equals(ERROR)) {
            //异常数据返回空
            rowList.add("");
        } else {
            rowList.add("");
        }
    }

}
